﻿Imports DTO
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data.SqlTypes
Public Class SachDAO
     
    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From Sach"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function


    Public Function LayDanhSach() As List(Of SachDTO)
        Dim ds As New List(Of SachDTO)
        Dim cn As SqlConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From Sach"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Dim sach As New SachDTO()
        While (dr.Read())
            sach = New SachDTO()
            sach.MaSach = dr("MaSach")
            sach.TenSach = dr("TenSach")
            ds.Add(sach)
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return ds
    End Function

    Public Function LayThongTin() As DataTable
        Dim dt As New DataTable
        Dim connect As SqlConnection
        connect = DatabaseProvider.ConnectionData()
        Dim sql As String
        sql = "Select s.TenSach,tl.TenTheLoai,tg.TenTacGia, s.SoLuongTon,s.GiaBan from Sach s, TheLoai tl,TacGia tg where s.MaTheLoai = tl.MaTheLoai and s.MaTacGia = tg.MaTacGia"
        Dim da As New SqlDataAdapter(sql, connect)
        da.Fill(dt)
        connect.Close()
        Return dt

    End Function


    Public Function LoadDauSach() As DataTable
        Dim ls As New DataTable()
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        Dim strSQL As String
        strSQL = "select s.masach, s.tensach, tg.tentacgia, tl.tentheloai, s.giaban, s.soluongton from Sach s, theloai tl, tacgia tg where s.matacgia=tg.matacgia and s.matheloai=tl.matheloai"
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(ls)
        cn.Close()
        Return ls
    End Function



    Public Sub Them(ByVal sachdto As SachDTO)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into SACH(MaTheLoai,MaTacGia,TenSach,GiaBan,SoLuongTon) values(@MaTheLoai,@MaTacGia,@TenSach,@GiaBan,@SoLuongTon)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)

        cmd.Parameters.Add("@MaTacGia", SqlDbType.Int)
        cmd.Parameters("@MaTacGia").Value = sachdto.MaTacGia

        cmd.Parameters.Add("@MaTheLoai", SqlDbType.Int)
        cmd.Parameters("@MaTheLoai").Value = sachdto.MaTheLoai

        cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar)
        cmd.Parameters("@TenSach").Value = sachdto.TenSach

        cmd.Parameters.Add("@GiaBan", SqlDbType.Int)
        cmd.Parameters("@GiaBan").Value = sachdto.GiaBan

        cmd.Parameters.Add("@SoLuongTon", SqlDbType.Int)
        cmd.Parameters("@SoLuongTon").Value = sachdto.SoLuongTon


        cmd.ExecuteNonQuery()


        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        sachdto.MaSach = Convert.ToInt32(cmd.ExecuteScalar())

        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub



    Public Sub CapNhat(ByVal sachDto As SachDTO)
        Dim cn As SqlConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update Sach Set tensach = @tensach Where masach = @masach"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)

        cmd.Parameters.Add("@MaTacGia", SqlDbType.Int)
        cmd.Parameters("@MaTacGia").Value = sachDto.MaTacGia

        cmd.Parameters.Add("@MaTheLoai", SqlDbType.Int)
        cmd.Parameters("@MaTheLoai").Value = sachDto.MaTheLoai

        cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar)
        cmd.Parameters("@TenSach").Value = sachDto.TenSach

        cmd.Parameters.Add("@GiaBan", SqlDbType.Int)
        cmd.Parameters("@GiaBan").Value = sachDto.GiaBan

        cmd.Parameters.Add("@SoLuongTon", SqlDbType.Int)
        cmd.Parameters("@SoLuongTon").Value = sachDto.SoLuongTon

        cmd.Parameters.Add("@masach", SqlDbType.Int)
        cmd.Parameters("@masach").Value = sachDto.MaSach


        cmd.ExecuteNonQuery()


        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal tenSach As SachDTO)
        Dim cn As SqlConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Delete From Sach Where tensach = @tensach "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)

        cmd.Parameters.Add("@tensach", SqlDbType.NVarChar)

        cmd.Parameters("@tensach").Value = tenSach.TenSach

        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

End Class
